#!/bin/bash
# -------------------------------------------------------------
# 自动创建 Docker TLS 证书
# -------------------------------------------------------------

# config
# --[BEGIN]------------------------------

# 代码，可以随便写
CODE="WRETCHANT"
# 服务器的外网IP
IP="123.57.249.112"
# CA证书的密码
PASSWORD="CA-PASSWORD.591511"
# 国家
COUNTRY="CN"
# 地区
STATE="zhejiang"
# 城市
CITY="hangzhou"
# 组织
ORGANIZATION="WRETCHANT.EDU"
ORGANIZATIONAL_UNIT="Dev"
COMMON_NAME="$IP"
# 邮件地址
EMAIL="neteasyxukang@163.com"

# --[END]--


# 创建存放脚本的文件夹
mkdir -p /etc/docker/cert.init/
cd /etc/docker/cert.init/


# Generate CA key
openssl genrsa -aes256 -passout "pass:$PASSWORD" -out "ca-key.pem" 4096
# Generate CA
openssl req -new -x509 -days 365 -key "ca-key.pem" -sha256 -out "ca.pem"   -passin "pass:$PASSWORD" -subj "/C=$COUNTRY/ST=$STATE/L=$CITY/O=$ORGANIZATION/OU=$ORGANIZATIONAL_UNIT/CN=$COMMON_NAME/emailAddress=$EMAIL"
# Generate Server key
openssl genrsa -out "server-key.pem" 4096

# Generate Server Certs.
openssl req -subj "/CN=$COMMON_NAME" -sha256 -new -key "server-key.pem" -out server.csr

echo "subjectAltName = IP:$IP,IP:127.0.0.1" >> extfile.cnf
echo "extendedKeyUsage = serverAuth" >> extfile.cnf

openssl x509 -req -days 365 -sha256 -in server.csr -passin "pass:$PASSWORD" -CA "ca.pem" -CAkey "ca-key.pem" -CAcreateserial -out "server-cert.pem" -extfile extfile.cnf

# Generate Client Certs.
rm -f extfile.cnf

openssl genrsa -out "key.pem" 4096
openssl req -subj '/CN=client' -new -key "key.pem" -out client.csr
echo extendedKeyUsage = clientAuth >> extfile.cnf
openssl x509 -req -days 365 -sha256 -in client.csr -passin "pass:$PASSWORD" -CA "ca.pem" -CAkey "ca-key.pem" -CAcreateserial -out "cert.pem" -extfile extfile.cnf

rm -vf client.csr server.csr

chmod -v 0400 "ca-key.pem" "key.pem" "server-key.pem"
chmod -v 0444 "ca.pem" "server-cert.pem" "cert.pem"

# 打包客户端证书成tar.gz包
mkdir -p "tls-client-certs"
cp -f "ca.pem" "cert.pem" "key.pem" "tls-client-certs/"
cd "tls-client-certs"
tar zcf "tls-client-certs.tar.gz" *
mv "tls-client-certs.tar.gz" ../
cd ..
rm -rf "tls-client-certs"

# 拷贝服务端证书，保存在docker目录下
mkdir -p /etc/docker
cp "ca.pem" "server-cert.pem" "server-key.pem" /etc/docker


--tlsverify \

--tlscacert=/etc/docker/ca.pem \

--tlscert=/etc/docker/server-cert.pem \

--tlskey=/etc/docker/server-key.pem \

-H tcp://0.0.0.0:2376 \

-H unix:///var/run/docker.sock \


















解决Mysql8+版本远程连接问题（亲测有效）

向小凯同学学习 2019-04-04 15:13:06  6068  收藏 36
分类专栏： mysql数据库 文章标签： Mysql 远程访问 2059 - Authentication plugin 'cachi 1130 - Host '10.45.12.79' is not al 权限、创建用户
版权

mysql数据库
专栏收录该内容
9 篇文章1 订阅
订阅专栏
在安装的Mysql 8+版本后远程连接不上
注意：每次修改后，涉及到权限的一定要刷新权限。

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.04 sec)

mysql>
1
2
3
4
会报一个错误：

1130 - Host '10.45.12.79' is not allowed to connect to this MySQL server
1


一、查看Mysql是否开启可以远程访问的权限
1.登录mysql机器
mysql -u root -p
1


2.切换到Mysql数据库
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>
1
2
3
4
5
6
3.查看是否运行远程访问
可以看到第一行root 用户的host为localhost，要远程访问，需要将它改成%

mysql> select host,user,plugin from user;
+-----------+------------------+-----------------------+
| host      | user             | plugin                |
+-----------+------------------+-----------------------+
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | caching_sha2_password |
| localhost | mysql.sys        | caching_sha2_password |
| localhost | root             | caching_sha2_password |
+-----------+------------------+-----------------------+
4 rows in set (0.00 sec)

mysql>
1
2
3
4
5
6
7
8
9
10
11
12
4.将host改为%
mysql> update user set host='%' where user ='root';
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select host,user,plugin from user;
+-----------+------------------+-----------------------+
| host      | user             | plugin                |
+-----------+------------------+-----------------------+
| %         | root             | caching_sha2_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | caching_sha2_password |
| localhost | mysql.sys        | caching_sha2_password |
+-----------+------------------+-----------------------+
4 rows in set (0.00 sec)

mysql>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
5.刷新权限
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.10 sec)

mysql>
1
2
3
4
现在有权限访问了，但是校验方式又有了问题

2059 - Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(../Frameworks/caching_sha2_password.so, 2): image not found
1


二、更改连接的密码校验方式
caching_sha2_password加密方式在远程访问时候不支持。
需要改成：mysql_native_password


1.更改连接方式
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.09 sec)

mysql> select host,user,plugin from user;
+-----------+------------------+-----------------------+
| host      | user             | plugin                |
+-----------+------------------+-----------------------+
| %         | root             | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | caching_sha2_password |
| localhost | mysql.sys        | caching_sha2_password |
+-----------+------------------+-----------------------+
4 rows in set (0.00 sec)

mysql>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2.刷新权限
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.04 sec)

mysql>
1
2
3
4
3.远程连接
OK了


三、创建用户并授权
1.创建用户
Mysql 8 在创建用户上面，有了很大的不同
Mysql 8 之前的创建方式在这里会报错

mysql> GRANT ALL ON *.* TO `wangwei`@`127.0.0.1` IDENTIFIED BY 'passowrd' WITH GRANT OPTION;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'passowrd' WITH GRANT OPTION' at line 1
1
2
Mysql 8正确的创建方式

mysql> CREATE USER `developer`@`%` IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.08 sec)

mysql> select host,user,plugin from user;
+-----------+------------------+-----------------------+
| host      | user             | plugin                |
+-----------+------------------+-----------------------+
| %         | developer        | caching_sha2_password |
| %         | root             | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | caching_sha2_password |
| localhost | mysql.sys        | caching_sha2_password |
+-----------+------------------+-----------------------+
5 rows in set (0.00 sec)

mysql>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2.授权
mysql> GRANT ALL ON *.* TO `developer`@`%` WITH GRANT OPTION;
Query OK, 0 rows affected (0.09 sec)

mysql> select host,user,plugin from user;
+-----------+------------------+-----------------------+
| host      | user             | plugin                |
+-----------+------------------+-----------------------+
| %         | developer        | caching_sha2_password |
| %         | root             | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | caching_sha2_password |
| localhost | mysql.sys        | caching_sha2_password |
+-----------+------------------+-----------------------+
5 rows in set (0.00 sec)

mysql>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
发现用户developer的加密连接方式plugin不对，修改一下
最后一定要刷新权限

mysql> ALTER USER 'developer'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.08 sec)

mysql> select host,user,plugin from user;
+-----------+------------------+-----------------------+
| host      | user             | plugin                |
+-----------+------------------+-----------------------+
| %         | developer        | mysql_native_password |
| %         | root             | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | caching_sha2_password |
| localhost | mysql.sys        | caching_sha2_password |
+-----------+------------------+-----------------------+
5 rows in set (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.04 sec)

mysql>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
3.连接


好了，可以愉快的开发了！
————————————————
版权声明：本文为CSDN博主「向小凯同学学习」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/wd2014610/article/details/89023562
